# -*- coding: UTF-8 -*-
import json
import random
import re
import time
import ssl
from bs4 import BeautifulSoup as bs
import muggle_ocr
import requests
import hashlib
import codecs
import pymysql

my_url = "http://my.scu.edu.cn/userPasswordValidate.portal"  # 这个是自己的网址
captcha_url = "http://zhjw.scu.edu.cn/img/captcha.jpg"  # 验证码地址
index_url = "http://zhjw.scu.edu.cn/"  # 主页地址，登录之后的主页
index_url = "http://zhjw.scu.edu.cn/index.jsp"  # 但是和上面的其实指向一个地址
login_url = "http://zhjw.scu.edu.cn/j_spring_security_check"  # 登录接口，登录界面提交之后的处理者
already_select_course_url = "http://zhjw.scu.edu.cn/student/courseSelect/thisSemesterCurriculum/callback"  # 已选课程查询地址，但是callback会返回json
header = {
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9',
    'Accept-Encoding': 'gzip, deflate',
    'Accept-Language': 'zh-CN,zh;q=0.9',
    'Connection': 'keep-alive',
    'DNT': '1',
    'Host': 'zhjw.scu.edu.cn',
    'Upgrade-Insecure-Requests': '1',
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3782.0 Safari/537.36 Edg/76.0.152.0'
}
# 这部分用到了自己的网址
my_header = {
    "Host": "my.scu.edu.cn",
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:89.0) Gecko/20100101 Firefox/89.0",
    "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8",
    "Accept-Language": "zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2",
    "Accept-Encoding": "gzip, deflate",
    "Content-Type": "application/x-www-form-urlencoded",
    "Content-Length": "160",
    "Origin": "http://my.scu.edu.cn",
    "Connection": "keep-alive",
    #    "Referer": "http://my.scu.edu.cn/",
    "Upgrade-Insecure-Requests": "1"
}
sdk = muggle_ocr.SDK(model_type=muggle_ocr.ModelType.Captcha)  # 识别验证码


# 读取密码，可以让用户输入
with open("config.txt", "r", encoding='utf-8') as f:
    info = f.readlines()

j_username = info[0].strip('\n')  # 用户名
# md5加密……，暂时没必要，但是为了安全性可以作为进化功能
# j_password = hashlib.md5(info[1].strip('\n').encode()).hexdigest()
j_password = info[1].strip('\n')  # 密码
with open("config.txt", "w", encoding='utf-8') as f:
    f.truncate()

# print(j_username)
# print(j_password)


def login(session):
    captcha_bytes = session.get(url=captcha_url, headers=header).content
    text = sdk.predict(image_bytes=captcha_bytes)
    if len(text) != 4:
        return None
    login_data = {
        'j_username': j_username,
        'j_password': hashlib.md5(j_password.strip('\n').encode()).hexdigest(),
        'j_captcha': text
    }
    # print(login_data)
    try:
        response = session.post(url=login_url, headers=header, data=login_data).text
        if "欢迎您" in response:
            print("登陆成功！")
            return "success"
        else:
            return "failed"
    except Exception as e:
        print("def login() 出现问题:" + str(e))
        time.sleep(1)
        return None


# 这个函数用来获取所有的已选课程的信息
def getAlreadyCourseInfo(session):
    already_select_course_info_list = []
    # try:
    response = session.get(url=already_select_course_url, headers=header).text
    # 对其分析，看我们能获得那些数据
    # with open("now.txt","w") as f:
    #     f.write(response)
    # 第一部分是总学分，xkkx里面存放的是课程信息
    all_about_class = json.loads(response)
    allUnits = all_about_class["allUnits"]  # 总学分
    res_all_class = all_about_class['xkxx'][0]
    for each in res_all_class:
        class_cid = res_all_class[each]['id']['coureNumber']  # 课程号
        class_sno = res_all_class[each]['id']['coureSequenceNumber']  # 课序号
        class_name = res_all_class[each]['courseName']  # 课程名称
        class_teacher = res_all_class[each]['attendClassTeacher'].strip()[0:-1]  # 课程教师
        class_type = res_all_class[each]['courseCategoryName']  # 课程属性,指的是公选课，国际周等
        class_value = res_all_class[each]['unit']  # 学分
        class_week = class_day = class_start = class_continue = class_campus = class_buliding = class_room = ' '
        # 这些值可能为空
        try:
            class_week = res_all_class[each]['timeAndPlaceList'][0]['weekDescription']  # 上课周次
        except Exception as e:
            pass
        try:
            class_day = res_all_class[each]['timeAndPlaceList'][0]['classDay']  # 星期x
        except Exception as e:
            pass
        try:
            class_start = res_all_class[each]['timeAndPlaceList'][0]['classSessions']  # 开始节次
        except Exception as e:
            pass
        try:
            class_continue = res_all_class[each]['timeAndPlaceList'][0]['continuingSession']  # 持续节次
        except Exception as e:
            pass
        try:
            class_campus = res_all_class[each]['timeAndPlaceList'][0]['campusName']  # 校区
        except Exception as e:
            pass
        try:
            class_buliding = res_all_class[each]['timeAndPlaceList'][0]['teachingBuildingName']  # 教学楼
        except Exception as e:
            pass
        try:
            class_room = res_all_class[each]['timeAndPlaceList'][0]['classroomName']  # 教室
        except Exception as e:
            pass

        already_select_course_info_list.append([
            class_cid, class_sno, class_name, class_teacher,
            class_type, class_value,
            class_week, class_day, class_start, class_continue,
            class_campus, class_buliding, class_room
            # 所属专业暂时没有找到，暂时考虑放弃这一列
        ])
    return allUnits, already_select_course_info_list


# 创建数据库与插入数据
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '124938'  # 不设置密码，为空
DBNAME = 'Schedule'  # 数据库名字

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)  # 连接数据库
cur = db.cursor()  # 声明游标

# 判断表是否存在
def table_exists(con, table_name):  # 这个函数用来判断表是否存在
    sql = "show tables;"
    con.execute(sql)
    tables = [con.fetchall()]
    table_list = re.findall('(\'.*?\')', str(tables))
    table_list = [re.sub("'", '', each) for each in table_list]
    if table_name in table_list:
        return 1  # 存在返回1
    else:
        return 0  # 不存在返回0


# 创建数据表
def create(session):
    try:
        # cur.execute("DROP TABLE IF EXISTS activities")  # 创建数据表 Class

        # 建立class表
        # sql语句
        if (table_exists(cur, 'class') != 1):
            sql = 'CREATE TABLE CLASS (CID char(10),CSN char (4),CNAME varchar (40),TNAME varchar (25),CTYPE varchar (10),CUNIT char(4), WEEK varchar (10), DAY varchar(10),BEGINSESSION varchar(10),CONTINUESESSION varchar(4),CAMPUS varchar(10),BUILDINGNAME varchar(20),ROOM varchar (10),primary key (CID,CSN))'
            cur.execute(sql)
        # 创建stuclass 表
        if (table_exists(cur, 'stuclass') != 1):
            sql = 'CREATE TABLE stuCLASS (sCID char(10),sCSN char (4),sCNAME varchar (40),sTNAME varchar (25),sCTYPE varchar (10),sCUNIT char(4), sWEEK varchar (10), sDAY varchar(10),sBEGINSESSION varchar(10),sCONTINUESESSION varchar(4),sCAMPUS varchar(10),sBUILDINGNAME varchar(20),sROOM varchar (10),primary key (sCID,sCSN))'
            cur.execute(sql)
        # 建立usr表
        if (table_exists(cur, 'usr') != 1):
            sql = 'create table USR(UID char (20) primary key,UINFO char(4),UNAME varchar (20) ,UPW varchar (20))'
            cur.execute(sql)

        # 建立contact表
        if (table_exists(cur, 'contact') != 1):
            sql = 'create table CONTACT(UID char (20),CID char (10),CSN char (4),foreign key (UID) references USR(UID) on delete cascade,foreign key (CID) references CLASS(CID) on delete cascade)'
            cur.execute(sql)
        # 建立mag表
        if (table_exists(cur, 'mag') != 1):
            sql = 'create table  MAG(MID char (10) primary key,MNAME varchar (20) ,MPW char (10))'
            cur.execute(sql)
        # 建立note表
        if (table_exists(cur, 'note') != 1):
            sql = 'create table  note(NID varchar (4) primary key,title varchar (20),stutext varchar(200))'
            cur.execute(sql)

        # 建立notice表
        if (table_exists(cur, 'notice') != 1):
            sql = 'create table  notice(sCID char (10),sCSN char (4),teatext varchar(200))'
            cur.execute(sql)

        print("数据表创建成功！")
    except pymysql.Error as e:
        print("数据表创建失败！" + str(e))


# 插入数据
def insert(session, allclass,user_name):

    with open('course.json', 'r', encoding='utf-8') as f:
        course_text = json.load(f)
    val = []
    for each_part in course_text['list']['records']:
        val.append(each_part['kch'])
        val.append(each_part['kxh'])
        val.append(each_part['kcm'])
        val.append(each_part['skjs'])
        val.append(each_part['xkmssm'])

        val.append(each_part['xf'])
        val.append(each_part['zcsm'])
        val.append(each_part['skxq'])
        val.append(each_part['skjc'])
        val.append(each_part['cxjc'])

        val.append(each_part['xqm'])
        val.append(each_part['jxlm'])
        val.append(each_part['jasm'])
        sql = 'INSERT INTO class VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'  # 插入数据
        try:
            cur.execute(sql, val)
            db.commit()
        except:
            db.rollback()
        val.clear()


    # 查看用户表中是否有该学生记录
    cur.execute('SELECT COUNT(*) FROM usr WHERE uid=%s' % j_username)
    result = cur.fetchall()
    # print(j_username, j_password)
    if result[0][0] == 0:  # 没有该结果，执行插入操作
        val = []
        val.append(j_username)  # 插入学号
        if (len(j_username) == 13):  # 视情况插入下标，0代表学生，1代表教师
            val.append('0')
        else:
            val.append('1')
        val.append(user_name)  # 插入名字
        val.append(j_password)  # 插入密码
        sql = 'INSERT INTO usr VALUES(%s,%s,%s,%s)'  # 插入数据
        try:
            cur.execute(sql, val)
            db.commit()
            print('插入usr数据成功')
        except:
            db.rollback()
            print("插入usr数据失败")
        val.clear()


        # 插入stuclass
        for each_course_info in allclass:
            for i in range(13):
                val.append(each_course_info[i])
            sql = 'INSERT INTO stuclass VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'  # 插入数据
            try:
                cur.execute(sql, val)
                db.commit()
                print('插入stuclass数据成功')
            except:
                db.rollback()
                print("插入stuclass数据失败")
            val.clear()


        # 插入contact
        for each_course_info in allclass:
            val.append(j_username)
            val.append(each_course_info[0]) # 插入课程号
            val.append(each_course_info[1]) # 插入课序号
            sql = 'INSERT INTO contact VALUES(%s,%s,%s)'  # 插入数据
            try:
                cur.execute(sql, val)
                db.commit()
                print('插入contact数据成功')
            except:
                db.rollback()
                print("插入contact数据失败")
            val.clear()

        #插入mag
    else:{

    }


def main(session):
    # ------------login-------------
    while True:
        # 登录
        loginResponse = login(session)
        if loginResponse == "success":
            break
        else:
            print("登陆失败！")
            time.sleep(1)
    # ----------login_end------------


    # -----------------------用户信息-----------------------------------
    get_info = bs(session.get(index_url, headers=header).text, "html.parser")
    # print(get_info)

    user_info = get_info.find("span", class_="user-info")
    # print(user_info)

    to_find_name = re.compile(r'</small>(.*?)</span>', re.DOTALL)
    user_info = str(user_info)
    # print(user_info)
    user_name = to_find_name.findall(user_info)[0].strip()

    allunits, allclass = getAlreadyCourseInfo(session)
    create(session)  # 创建表
    insert(session, allclass,user_name)  # 插入数据

    # # -----------------------已选课程-----------------------------------
    # print("学号：" + j_username)
    # print("姓名：" + user_name)
    # print("总学分 " + str(allunits))
    # print("\n=====================已选课程如下=====================\n")
    # for each_course_info in allclass:
    #     print("课程号:" + each_course_info[0] + " 课序号:" + each_course_info[1] +
    #           " 课程名:" + each_course_info[2] + " 教师:" + each_course_info[3] +
    #           " 课程属性:" + each_course_info[4] + " 学分:" + str(each_course_info[5]) +
    #           " 周次:" + each_course_info[6] + " 星期:" + str(each_course_info[7]) +
    #           " 节次:" + str(each_course_info[8]) + " 持续节数:" + str(each_course_info[9]) +
    #           "校区:" + each_course_info[10] + " 教学楼:" + each_course_info[11] +
    #           " 教室:" + each_course_info[12]
    #           )
    # print("\n=====================已选课程如上=====================\n")


if __name__ == "__main__":
    # 使用session的原因是能够保存cookie，也就是说，输入密码之后再次访问网页能允许你进入！
    session = requests.session()
    main(session)
